package queries;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import entitys.Product;
import enums.Result;

public class GetProductsListQuery {
/**
 * Execute query
 * @param conn
 * @return
 */
	public static ArrayList<Product> exe(Connection conn) {
		ArrayList<Product> arr = new ArrayList<Product>();
		ResultSet res = null; 
		Statement stmt;
		
			
			try {
				stmt = conn.createStatement();
				res = stmt.executeQuery("SELECT * from ccrm.product; ");
				while(res.next())
				{
					
					arr.add(new Product(res.getString(2), res.getInt(1),res.getString(4)));
				//	arr.add(new Product("AA", 1));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		return arr;
		
	}
	/**
	 * Execute query By name
	 * @param conn
	 * @param str
	 * @return
	 */
	public static ArrayList<Product> exe(Connection conn,String str) { //with sector and type condition
		ArrayList<Product> arr = new ArrayList<Product>();
		ResultSet res = null; 
		Statement stmt;
		int sector,type;
		int indexOfS;
		
		indexOfS=str.indexOf('@');
		sector=Integer.parseInt(str.substring(0,indexOfS));
		type=Integer.parseInt(str.substring(indexOfS+1,str.length()));
		
			try {
				stmt = conn.createStatement();
				/*
				stmt.executeQuery( "DROP VIEW A; ");
				stmt.executeQuery("CREATE VIEW B AS SELECT (productintype.productID as pid) from ccrm.productintype where"
						+ " productintype.typeID=(SELECT type.id from ccrm.type where type.name='"+type+"') AND "
						+ " productintype.categoryID=(SELECT category.id from ccrm.category where category.name='"+sector+"');");
						*/	
				if (sector==-1 && type ==-1)
					res = stmt.executeQuery( "SELECT * from ccrm.product");
				else
					if(sector==-1)
						res = stmt.executeQuery( "SELECT * from ccrm.product where product.id IN (select productintype.productID from ccrm.productintype where typeID="+type+")  ; ");
					else
						if(type==-1)
							res = stmt.executeQuery( "SELECT * from ccrm.product where product.id IN (select productintype.productID from ccrm.productintype where categoryID="+sector+")  ; ");
						else
							res = stmt.executeQuery( "SELECT * from ccrm.product where product.id IN (select productintype.productID from ccrm.productintype where typeID="+type+" AND categoryID="+sector+")  ; ");
							
				
				while(res.next())
				{
					
					arr.add(new Product(res.getString(2), res.getInt(1),res.getString(4)));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		return arr;
		
	}
	
}
